﻿using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Data.SqlClient;
using Libraries.Common;
using Libraries.Entities;

namespace Libraries.Repositories
{
    public class AccountRepository
    {
        public ICollection<ContractType> FetchAllContractType() {
            var contractTypes = new List<ContractType>();
            if(!string.IsNullOrEmpty(ProcedureConfiguration.FetchAllContractType)) {
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.FetchAllContractType, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        while (result.Read())
                        {
                            contractTypes.Add(new ContractType {
                                                                   ContractTypeId = result["ContractTypeId"].ToInt32OrDefault(),
                                                                   ContractTypeName = result["ContractTypeName"].ToStringOrDefault()
                                                               });
                        }
                    }
                }
            }
            return contractTypes;
        }

        public ICollection<Location> FetchAllLocation()
        {
            var locations = new List<Location>();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.FetchAllLocation))
            {
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.FetchAllLocation, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        while (result.Read()) {
                            locations.Add(new Location {
                                                           LocationId = result["LocationId"].ToInt32OrDefault(),
                                                           LocationName = result["LocationName"].ToStringOrDefault()
                                                       });
                        }
                    }
                }
            }
            return locations;
        }

        public ICollection<JobTitle> FetchAllJobTitle()
        {
            var jobTitles = new List<JobTitle>();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.FetchAllJobTitle))
            {
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.FetchAllJobTitle, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        while (result.Read()) {
                            jobTitles.Add(new JobTitle {
                                                           JobTitleId = result["JobTitleId"].ToInt32OrDefault(),
                                                           JobTitleName = result["JobTitleName"].ToStringOrDefault()
                                                       });
                        }
                    }
                }
            }
            return jobTitles;
        }

        public ICollection<Account> PagingAccount(string accountName, string fullName,
            int? locationId, int? jobTitleId, string employeeId, int? departmentId,
            int? contractTypeId, int? statusAccountId, int minMonth, int maxMonth,
            int pageNumber = 1, int pageSize = 10) {
            var parameters = new Collection<SqlParameter> {
                                                              new SqlParameter("@AccountName", string.IsNullOrEmpty(accountName) ? DBNull.Value : (object)accountName),
                                                              new SqlParameter("@FullName", string.IsNullOrEmpty(fullName) ? DBNull.Value :(object)fullName),
                                                              new SqlParameter("@LocationId", (object)locationId ?? DBNull.Value),
                                                              new SqlParameter("@JobTitleId", (object)jobTitleId ?? DBNull.Value),
                                                              new SqlParameter("@EmployeeId", string.IsNullOrEmpty(employeeId) ? DBNull.Value : (object)employeeId),
                                                              new SqlParameter("@DepartmentId", departmentId ?? 0),
                                                              new SqlParameter("@ContractTypeId", (object)contractTypeId ?? DBNull.Value),
                                                              new SqlParameter("@StatusAccountId", (object)statusAccountId ?? DBNull.Value),
                                                              new SqlParameter("@MinMonth", minMonth),
                                                              new SqlParameter("@MaxMonth", maxMonth),
                                                              new SqlParameter("@PageNumber", pageNumber),
                                                              new SqlParameter("@PageSize", pageSize)
                                                          };
            var accounts = new Collection<Account>();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.PagingAccount)) {
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.PagingAccount, parameters, ConnectionConfiguration.ConnectionString)) {
                    if (result != null) {
                        while (result.Read()) {
                            accounts.Add(ParseToEntityPagingAccount(result));
                        }
                    }
                }
            }
            return accounts;
        }

        public Account GetAccountDetailsById(string employeeId) {
            var parameters = new Collection<SqlParameter> {
                                                              new SqlParameter("@EmployeeId", employeeId)
                                                          };
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetAccountDetailsById)) {
                var account = new Account();
                var accountExist = false;
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetAccountDetailsById, parameters, ConnectionConfiguration.ConnectionString)) {
                    if (result.Read()) {
                        accountExist = true;
                        var department = new Department {
                                                            DepartmentId = result["DepartmentId"].ToInt32OrDefault(),
                                                            DepartmentName = result["DepartmentName"].ToStringOrDefault()
                                                        };
                        var location = new Location {
                                                        LocationId = result["LocationId"].ToInt32OrDefault(),
                                                        LocationName = result["LocationName"].ToStringOrDefault()
                                                    };
                        var jobTitle = new JobTitle {
                                                        JobTitleId = result["JobTitleId"].ToInt32OrDefault(),
                                                        JobTitleName = result["JobTitleName"].ToStringOrDefault()
                                                    };
                        var jobFunction = new JobFunction {
                                                              JobFunctionId = result["JobFunctionId"].ToInt32OrDefault(),
                                                              JobFunctionName = result["JobFunctionName"].ToStringOrDefault()
                                                          };
                        var contractType = new ContractType {
                                                                ContractTypeId = result["ContractTypeId"].ToInt32OrDefault(),
                                                                ContractTypeName = result["ContractTypeName"].ToStringOrDefault()
                                                            };
                        var level = new Level {
                                                  LevelId = result["LevelId"].ToInt32OrDefault(),
                                                  LevelName = result["LevelName"].ToStringOrDefault()
                                              };
                        var rank = new Rank {
                                                RankId = result["RankId"].ToInt32OrDefault(),
                                                RankName = result["RankName"].ToStringOrDefault()
                                            };
                        var role = new Role {
                                                RoleId = result["RoleId"].ToInt32OrDefault(),
                                                RoleName = result["RoleName"].ToStringOrDefault()
                                            };
                        var officerCode = new OfficerCode {
                                                              OfficerCodeId = result["OfficerCodeId"].ToInt32OrDefault(),
                                                              OfficerCodeName = result["OfficerCodeName"].ToStringOrDefault()
                                                          };
                        var statusAccount = new StatusAccount {
                                                                  StatusAccountId = result["StatusAccountId"].ToInt32OrDefault(),
                                                                  StatusAccountName = result["StatusAccountName"].ToStringOrDefault()
                                                              };

                        account.EmployeeId = result["EmployeeId"].ToStringOrDefault();
                        account.AccountName = result["AccountName"].ToStringOrDefault();
                        account.FullName = result["FullName"].ToStringOrDefault();
                        account.MobilePhone = result["MobilePhone"].ToStringOrDefault();
                        account.Address = result["Address"].ToStringOrDefault();
                        account.BOD = result["BOD"].ToDateTimeOrNull();
                        account.HireDate = result["HireDate"].ToDateTimeOrNull();
                        account.OutOfDate = result["OutOfDate"].ToDateTimeOrNull();
                        account.Department = department;
                        account.Location = location;
                        account.JobTitle = jobTitle;
                        account.JobFunction = jobFunction;
                        account.ContractType = contractType;
                        account.Level = level;
                        account.Rank = rank;
                        account.Role = role;
                        account.OfficerCode = officerCode;
                        account.StatusAccount = statusAccount;
                        account.Descriptions = result["Descriptions"].ToStringOrDefault();
                    }
                }

                if (accountExist) {
                    // Get Image
                    var accountImage = GetAccountImage(employeeId);
                    account.FileName = accountImage.FileName;
                    account.FileType = accountImage.FileType;
                    account.Image = accountImage.Image;

                    // Get all skills
                    account.AccountSkills = GetAccountSkills(employeeId);

                    // Get all certificates
                    account.CertificateRefunds = AccountCertificateRefund(employeeId);

                    // Get all courses
                    account.CourseRefunds = AccountCourseRefund(employeeId);

                    // Get language scores history
                    var languageScoreRepository = new LanguageScoreRepository();
                    account.LanguageScores = languageScoreRepository.GetLanguageScoreHistoryByEmployeeId(employeeId);
                }

                return account;
            }
            return default(Account);
        }

        private IList<AccountSkill> GetAccountSkills(string employeeId) {
            var parameters = new Collection<SqlParameter> {
                                                              new SqlParameter("@EmployeeId", employeeId)
                                                          };
            var accountSkills = new List<AccountSkill>();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetEmployeeSkills)) {
                using (IDataReader skills = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetEmployeeSkills, parameters, ConnectionConfiguration.ConnectionString)) {
                    if (skills != null) {
                        Skill skill;
                        while (skills.Read()) {
                            skill = new Skill {
                                                  SkillId = skills["SkillId"].ToInt32OrDefault(),
                                                  SkillName = skills["SkillName"].ToStringOrDefault()
                                              };
                            accountSkills.Add(new AccountSkill {
                                                                   AccountSkillId = skills["AccountSkillId"].ToInt32OrDefault(),
                                                                   Skill = skill,
                                                                   Descriptions = skills["Descriptions"].ToStringOrDefault()
                                                               });
                        }
                    }
                }
            }
            return accountSkills;
        }

        private Account GetAccountImage(string employeeId) {
            var parameters = new Collection<SqlParameter> {
                                                              new SqlParameter("@EmployeeId", employeeId)
                                                          };
            Account account = new Account();
            if (!string.IsNullOrEmpty(ProcedureConfiguration.GetAccountImage)) {
                using (IDataReader image = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.GetAccountImage, parameters, ConnectionConfiguration.ConnectionString)) {
                    if (image != null) {
                        if (image.Read()) {
                            account.FileName = image["FileName"].ToStringOrDefault();
                            account.FileType = image["FileType"].ToStringOrDefault();
                            account.Image = image["Contents"] == DBNull.Value ? null : (byte[])image["Contents"];
                        }
                    }
                }
            }
            return account;
        }

        private Account ParseToEntityPagingAccount(IDataRecord record) {
            if (record != null) {
                var location = new Location {
                                                LocationId = record["LocationId"].ToInt32OrDefault(),
                                                LocationName = record["LocationName"].ToStringOrDefault(),
                                            };
                var department = new Department {
                                                    DepartmentId = record["DepartmentId"].ToInt32OrDefault(),
                                                    DepartmentName = record["Department"].ToStringOrDefault()
                                                };
                var contractType = new ContractType {
                                                        ContractTypeId = record["ContractTypeId"].ToInt32OrDefault(),
                                                        ContractTypeName = record["ContractTypeName"].ToStringOrDefault()
                                                    };
                var jobTitle = new JobTitle {
                                                JobTitleId = record["JobTitleId"].ToInt32OrDefault(),
                                                JobTitleName = record["JobTitleName"].ToStringOrDefault()
                                            };
                var role = new Role {
                                        RoleId = record["RoleId"].ToInt32OrDefault(),
                                        RoleName = record["RoleName"].ToStringOrDefault()
                                    };
                var level = new Level {
                                          LevelId = record["LevelId"].ToInt32OrDefault(),
                                          LevelName = record["LevelName"].ToStringOrDefault()
                                      };
                var rank = new Rank {
                                        RankId = record["RankId"].ToInt32OrDefault(),
                                        RankName = record["RankName"].ToStringOrDefault()
                                    };
                var officerCode = new OfficerCode {
                                                      OfficerCodeId = record["OfficerCodeId"].ToInt32OrDefault(),
                                                      OfficerCodeName = record["OfficerCodeName"].ToStringOrDefault()
                                                  };
                var jobFunction = new JobFunction {
                                                      JobFunctionId = record["JobFunctionId"].ToInt32OrDefault(),
                                                      JobFunctionName = record["JobFunctionName"].ToStringOrDefault()
                                                  };
                var statusAccount = new StatusAccount {
                                                          StatusAccountId = record["StatusAccountId"].ToInt32OrDefault(),
                                                          StatusAccountName = record["StatusAccountName"].ToStringOrDefault()
                                                      };

                return new Account {
                                       EmployeeId = record["EmployeeId"].ToStringOrDefault(),
                                       AccountName = record["AccountName"].ToStringOrDefault(),
                                       FullName = record["FullName"].ToStringOrDefault(),
                                       Location = location,
                                       Department = department,
                                       ContractType = contractType,
                                       JobTitle = jobTitle,
                                       Role = role,
                                       Level = level,
                                       Rank = rank,
                                       OfficerCode = officerCode,
                                       JobFunction = jobFunction,
                                       BOD = record["BOD"].ToDateTimeOrNull(),
                                       MobilePhone = record["MobilePhone"].ToStringOrDefault(),
                                       Month = record["Month"].ToInt32OrDefault(),
                                       HireDate = record["HireDate"].ToDateTimeOrNull(),
                                       OutOfDate = record["OutOfDate"].ToDateTimeOrNull(),
                                       StatusAccount = statusAccount,
                                       Address = record["Address"].ToStringOrDefault(),
                                       Descriptions = record["Descriptions"].ToStringOrDefault(),
                                       TotalRows = record["TotalRows"].ToInt32OrDefault()
                                   };
            }
            return null;
        }

        public Collection<CourseRefund> AccountCourseRefund(string employeeId) {
            var parameter = new Collection<SqlParameter> {
                                                             new SqlParameter("@EmployeeId", employeeId)
                                                         };
            if (!string.IsNullOrEmpty(ProcedureConfiguration.AccountCourseRefund)) {
                var courseRefund = new Collection<CourseRefund>();
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.AccountCourseRefund, parameter, ConnectionConfiguration.ConnectionString)) {
                    if (result != null) {
                        while (result.Read()) {
                            var item = new CourseRefund();
                            item.Course = result["Course"].ToStringOrDefault();
                            item.TimeCommit = result["Time Commit"].ToInt32OrDefault();
                            item.EndDate = result["End Date"].ToDateTimeOrNull();
                            item.Currency = result["Currency"].ToStringOrDefault();
                            item.Costs = float.Parse(result["Costs"].ToString());
                            item.Refunds = float.Parse(result["Refunds"].ToString());
                            
                            courseRefund.Add(item);
                        }
                    }
                }
                return courseRefund;
            }
            return null;
        }

        public Collection<CertificateRefund> AccountCertificateRefund(string employeeId) {
            var parameter = new Collection<SqlParameter> {
                                                             new SqlParameter("@EmployeeId", employeeId)
                                                         };
            if (!string.IsNullOrEmpty(ProcedureConfiguration.AccountCertificateRefund))
            {
                var certificateRefund = new Collection<CertificateRefund>();
                using (IDataReader result = RepositorySql.SqlHelper.ExecuteReader(ProcedureConfiguration.AccountCertificateRefund, parameter, ConnectionConfiguration.ConnectionString))
                {
                    if (result != null)
                    {
                        while (result.Read())
                        {
                            var item = new CertificateRefund();
                            item.CertificateId = (int)result["CertificateId"];
                            item.Certificate = result["Certificate"].ToStringOrDefault();
                            item.TimeCommit = result["Time Commit"].ToInt32OrDefault();
                            item.CompleteDate = result["Completed Date"].ToDateTimeOrNull();
                            item.Currency = result["Currency"].ToStringOrDefault();
                            item.Fee = float.Parse(result["Fee"].ToString());
                            item.Refunds = float.Parse(result["Refunds"].ToString());
                            item.Provider = result["Provider"].ToStringOrDefault();

                            certificateRefund.Add(item);
                        }
                    }
                }
                return certificateRefund;
            }
            return null;
        } 
    }
}